package com.foreknow.demo17;

import java.sql.*;

/**
 * 预处理（编译）
 */
public class DBUtil {
    private Connection conn;
    private ResultSet rs;
    private PreparedStatement pstmt;
    /**
     * 连接数据库
     */
    public Connection getConnection(){
        try {
            //1 .加载驱动程序
            Class.forName("com.mysql.jdbc.Driver");
            //2. 获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/basic8","root","123456");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 查询
     * select * from guestbook
     * select * from guestbook where name=? and pass=?
     * 存在的问题： ？不能确定有几个，也有可能一个都没有
     * 解决方案: 动态可变参数 Object...values = Object[]
     * @param sql
     * @return ResultSet 结果集
     */
    public ResultSet query(String sql,Object...values) throws SQLException {
        pstmt = conn.prepareStatement(sql); //获取预处理接口对象
        //对数组遍历,此循环可以将一个或多个？替换具体的值
        for (int i = 0;i<values.length;i++) {
            pstmt.setObject(i + 1,values[i]);
        }
        rs = pstmt.executeQuery();
        return rs;
    }

    public int dml(String sql,Object...values) throws SQLException {
        pstmt = conn.prepareStatement(sql); //获取预处理接口对象
        //对数组遍历,此循环可以将一个或多个？替换具体的值
        for (int i = 0;i<values.length;i++) {
            pstmt.setObject(i + 1,values[i]);
        }
        int i = pstmt.executeUpdate();
        return i;
    }

    public static void main(String[] args) {
        DBUtil dbUtil = new DBUtil();
        try {
            Connection connection = dbUtil.getConnection(); //连接数据库
            //Jdbc中事务默认是自动提交的。通过Connection接口中的方法设置 setAutoCommit(true)
            connection.setAutoCommit(false); //事务设置为手动提交
            //            ResultSet rs = dbUtil.query("select * from guestbook where name=? and pass=?","fender","123456"); //调用查询的方法
            //            if(rs.next()) {
            //                String name = rs.getString("name");
            //                String pass = rs.getString("pass");
            //                System.out.println(name + "---" + pass);
            //            }
            int i = dbUtil.dml("insert into guestbook(name,pass) values(?,?)","zzzzz","123456");
            //提交事务
            connection.commit();
            if(i == 1) {
                System.out.println("success......");
            }else {
                System.out.println("error......");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        try {
//            Class.forName("com.mysql.jdbc.Driver");
//            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/basic8","root","123456");
//            PreparedStatement pstmt = conn.prepareStatement("insert into guestbook(name,pass) values(?,?)");
//            pstmt.setString(1,"xxx"); // 1表示第几个？
//            pstmt.setString(2,"123456");
//            int i = pstmt.executeUpdate();
//            System.out.println(i);
//            //执行查询
//            //            ResultSet rs = pstmt.executeQuery(); //将查询到的一条数据保存到结果集中
//            //            if(rs.next()) {
//            //                String name = rs.getString("name");
//            //                String pass = rs.getString("pass");
//            //                System.out.println(name + "---" + pass);
//            //            }
//
//        } catch (ClassNotFoundException e) {
//            e.printStackTrace();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
    }
}
